/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package edu.facilites.dao;

import edu.facilites.model.ReserveRecord;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 预留记录数据库操作表
 * 
 * @author user
 */
public class ReserveRecordDao extends BaseDao<ReserveRecord, Integer> {

    @Override
    public int saveOrUpdate(ReserveRecord t) throws Exception {
        StringBuilder sql = new StringBuilder();
        if(t.getId() == 0) {
            sql.append("insert into reserveRecord(facilityID, userID, startdate, enddate, createDate) values(");
            sql.append(t.getFacilityID());
            sql.append(",");
            sql.append(t.getUserID());
            sql.append(",'");
            sql.append(t.getStartDate());
            sql.append("','");
            sql.append(t.getEndDate());
            sql.append("','");
            sql.append(t.getCreateDate());
            sql.append("')");
        }else {
            sql.append("update reserveRecord set facilityID = ");
            sql.append(t.getFacilityID());
            sql.append(", userID = ");
            sql.append(t.getUserID());
            sql.append(", startdate = '");
            sql.append(t.getStartDate());
            sql.append("', enddate = '");
            sql.append(t.getEndDate());
            sql.append("', createdate = '");
            sql.append(t.getCreateDate());
            sql.append("' where id = ");
            sql.append(t.getId());
        }
        PreparedStatement ps = getPreparedStatement(sql.toString());
        return ps.executeUpdate();
    }

    @Override
    public int del(ReserveRecord t) throws Exception {
        PreparedStatement ps = getPreparedStatement("delete from reserveRecord where id = " + t.getId());
        return ps.executeUpdate();
    }

    @Override
    public ReserveRecord findById(Integer pk) throws Exception {
        PreparedStatement ps = getPreparedStatement("select * from reserveRecord where id = " + pk);
        ResultSet rs = ps.executeQuery();
        ReserveRecord reserveRecord = null;
        while (rs.next()) {            
            reserveRecord = new ReserveRecord();
            reserveRecord = getReserveRecord(rs, reserveRecord);
        }
        return reserveRecord;
    }

    @Override
    public List<ReserveRecord> findAll() throws Exception {
        PreparedStatement ps = getPreparedStatement("select * from reserveRecord");
        ResultSet rs = ps.executeQuery();
        ArrayList<ReserveRecord> list = new ArrayList<ReserveRecord>();
        while (rs.next()) {            
            ReserveRecord reserveRecord = new ReserveRecord();
            reserveRecord = getReserveRecord(rs, reserveRecord);
            list.add(reserveRecord);
        }
        return list;
    }
    
    /**
     * 查询某段时间内的某设备预约记录
     * @param facilityID
     * @param startDate
     * @param endDate
     * @return 
     */
    public List<ReserveRecord> findByfacilityIDAndDate(int facilityID, String startDate, String endDate) throws Exception{
        StringBuilder sql = new StringBuilder();
        if(facilityID == 0) {
            sql.append("select * from reserveRecord where ");
            sql.append(" startDate = '");
        }else {
            sql.append("select * from reserveRecord where facilityID = ");
            sql.append(facilityID);
            sql.append(", and startDate = '");
        }
        sql.append(startDate);
        sql.append("', and enddate = '");
        sql.append(endDate);
        sql.append("'");
        PreparedStatement ps = getPreparedStatement(sql.toString());
        ResultSet rs = ps.executeQuery();
        ArrayList<ReserveRecord> list = new ArrayList<ReserveRecord>();
        while(rs.next()) {
            ReserveRecord reserveRecord = new ReserveRecord();
            reserveRecord = getReserveRecord(rs, reserveRecord);
            list.add(reserveRecord);
        }
        return list;
    }
    
    
    /**
     * 得到预留记录对象
     * @param rs
     * @param reserveRecord
     * @return
     * @throws Exception 
     */
    public ReserveRecord getReserveRecord(ResultSet rs, ReserveRecord reserveRecord) throws Exception{
        reserveRecord.setId(rs.getInt("id"));
        reserveRecord.setUserID(rs.getInt("userid"));
        reserveRecord.setCreateDate(rs.getString("createdate"));
        reserveRecord.setEndDate(rs.getString("enddate"));
        reserveRecord.setStartDate(rs.getString("startdate"));
        reserveRecord.setFacilityID(rs.getInt("facilityid"));
        return reserveRecord;
    }

}
